Hi, I am trying to access linked server from my ssis sql command through OLE DB source.But I am getting an error exec [dbo].[usp_linkedserver] Error at Data Flow Task 1 [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Procedure or function 'usp_linkedserver' expects parameter '@ls_nsame', which was not supplied.". Error at Data Flow Task 1 [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available. Please solve the issue asap.Thanks

If you call your user-defined stored procedure, you need to provide values to parameters. Or refer to this link - http://msdn.microsoft.com/en-us/library/ms190479.aspx Please vote if it's helpful and mark it as an answer!

Description: "Procedure or function 'usp_linkedserver' expects parameter '@ls_nsame', which was not supplied.". Hello, You have a clear error message which shows you how to solve the "problem": You have to pass the parameter @ls_nsame to the stored procedureOlaf Helper Blog Xing

actually there is no need of parameter to pass as my stored proc is : ALTER proc [dbo].[usp_linkedserver] --( --@ls_nsame varchar(50) --) as begin declare @ls_Name varchar(2000) set @ls_Name = (SELECT ls_name FROM tb_lserver) --select @ls_Name execute('select * from ['+@ls_Name+'].[I_test].[dbo].[DB_customer]') end exec [dbo].[usp_linkedserver]//it is running through sql server but not through ssis sql command

I am surprised as to why are you trying to do this via linked server, i would have thought better you could have set a new OLDEDB connection to the serve and pass the server name via configuration file. Now if you needed to store this in SQL you could have used SQL configuration.Abhinav http://bishtabhinav.wordpress.com/

if you run it well through sql server, please check these : 1. make sure the server where you run your stored procedure is same with the server you connect through SSIS connection manager. 2. check your SSIS Task where you put your command inside, make sure you didn't configure any extra parameter in configuration pane. Please vote if it's helpful and mark it as an answer!

There is no problem in that...

I think its Linked Server related issue..... I'm also struck in this problem, when i'll get over it .... i'll revert to you.

Are you facing the same error message....

I'm sure you did some configuration in a wrong way, the message show it clearly 'Procedure or function 'usp_linkedserver' expects parameter '@ls_nsame', which was not supplied.". If you did run 'exec [dbo].[usp_linkedserver]' well in SQL Server, that means it OK in SQL Server. But actually if you configure it correctly, SSIS task wouldn't show this information to ask you provide @Is_nsame. So that's the reason why I ask you to check the server where you execute your stored procedure is the same with the server you configured in SSIS connection manager. Are they the same stored procedure you called in SQL Server and SSIS package? Or you configured a parameter for @Is_nsame but actually there's no parameter in this stored procedure. I can't image there's any other reason for this error message 'Procedure or function 'usp_linkedserver' expects parameter '@ls_nsame', which was not supplied.".', this shouldn't be a big problem. Please vote if it's helpful and mark it as an answer!

after creating new sp the error changed: TITLE: Microsoft Visual Studio ------------------------------ Error at Data Flow Task 1 [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Could not find stored procedure 'dbo.uspp_linkedserver'.". Error at Data Flow Task 1 [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

after creating new sp the error changed: TITLE: Microsoft Visual Studio ------------------------------ Error at Data Flow Task 1 [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Could not find stored procedure 'dbo.uspp_linkedserver'.". Error at Data Flow Task 1 [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available. uspp_linkedserver or usp_linkedserver ? Please vote if it's helpful and mark it as an answer!

Try out this..... go to Linked Server property and make sure that RPC out should be 'True' if not then make it as 'True' and then try again. I'm sure this will gonna help you..... If still exist then let me know.

create proc [dbo].[uspp_linkedserver] --( --@ls_nsame varchar(50) --) as begin declare @ls_Name varchar(2000) set @ls_Name = (SELECT ls_name FROM tb_lserver) --select @ls_Name execute('select * from ['+@ls_Name+'].[I_test].[dbo].[DB_customer]') end exec [dbo].[uspp_linkedserver] there is no problem with this in sql server..... But there is error after executing through ssis OLE DB sql command.....All procedures except those which haven't linked server are running successfully..................

By default its true...my friend its not that issue...

Linked Server is not recognizing the schema of the Table by default. So, You have to define it explicitly In Stored Procedur----- ALTER PROCEDURE [dbo].[usp_GetLinkedServerName] AS BEGIN SET NOCOUNT ON; if 1 = 0 select cast( 1 as VARCHAR(10)) SOURCEFILE declare @ls_name varchar(50) declare @sql varchar(2000) select @ls_name = [LinkedServer_value] FROM [I_test].[dbo].[tb_LinkedServer] set @sql = 'select SOURCEFILE FROM [' + @ls_name + '] .[S1013A4M].[dbo].[DTLBILLACT]' execute(@sql) END Please Mark it as answer if issue get resolve by this.

Thank you so much....:)